--1.--假设刘备取款6000，(添加check约束，设置账户余额必须>=0)，要求：使用事务实现，修改余额和添加取款记录两步操作使用事务
alter table BankCard add constraint CK_Money check(CardMoney >= 0)

declare @myError int = 0
declare @getMoney float = 6000
declare @carID varchar(10)
declare @name  varchar(10)
declare @Phone varchar(10)
set @name = '刘备'

select @carID = (select b.CardNo from AccountInfo a inner join BankCard b on a.AccountId =b.AccountId where RealName = @name)
select @Phone = (select a.AccountPhone from AccountInfo a inner join BankCard b on a.AccountId =b.AccountId where RealName = @name)

begin transaction A
update BankCard set CardMoney -= @getMoney where CardNo = @carID
set @myError += @@ERROR
insert into CardExchange values(@carID,0,@getMoney,getdate())
set @myError += @@ERROR

if(@myError = 0)
begin
	commit transaction A
end
else 
begin
	rollback transaction B
end
go

--2.--刘备向张飞转账1000元，(添加check约束，设置账户余额必须>=0)
alter table BankCard add constraint CK_Money check(CardMoney >= 0)
declare @myError int = 0
declare @getMoney float = 1000
declare @carID varchar(10)
declare @carID1 varchar(10)
declare @name  varchar(10)
declare @name1  varchar(10)
declare @Phone varchar(10)
declare @Phone1 varchar(10)
set @name = '刘备'
set @name1 = '张飞'
select @carID = (select b.CardNo from AccountInfo a inner join BankCard b on a.AccountId =b.AccountId where RealName = @name)
select @Phone = (select a.AccountPhone from AccountInfo a inner join BankCard b on a.AccountId =b.AccountId where RealName = @name)
select @carID1 = (select b.CardNo from AccountInfo a inner join BankCard b on a.AccountId =b.AccountId where RealName = @name1)
select @Phone1 = (select a.AccountPhone from AccountInfo a inner join BankCard b on a.AccountId =b.AccountId where RealName = @name1)

begin transaction
update BankCard set CardMoney -= @getMoney where CardNo = @carID
set @myError += @@ERROR
update BankCard set CardMoney += @getMoney where CardNo = @carID1
set @myError += @@ERROR
insert into CardTransfer values(@carID,@carID1,@getMoney,getdate())
set @myError += @@ERROR

if(@myError = 0)
begin
	commit transaction
end
else 
begin
	rollback transaction
end